﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;

namespace cashbook_psql_cs.util
{
    public static class ImportHelper
    {
        public static List<T> ReadExcelWithEPPlus<T>(string filePath) where T : new()
        {
            List<T> itemsList = new List<T>();
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                if (worksheet.Dimension == null) return itemsList;

                int rowCount = worksheet.Dimension.End.Row;
                int colCount = worksheet.Dimension.End.Column;

                // 列映射关系
                Dictionary<string, string> columnMappings = new Dictionary<string, string>
            {
                {"序号", "Id"},
                {"类别", "SZType"},
                {"金额", "Amount"},
                {"分类", "Cate"},
                {"账户", "Account"},
                {"时间", "SZTime"},
                {"成员", "RelPerson"},
                {"备注", "Remark"}
            };

                // 1. 获取标题行（第一行）的列名与索引的映射
                Dictionary<string, int> headers = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                for (int col = 1; col <= colCount; col++)
                {
                    string headerText = worksheet.Cells[1, col].Text?.Trim();
                    if (!string.IsNullOrEmpty(headerText) && columnMappings.ContainsKey(headerText))
                    {
                        headers[columnMappings[headerText]] = col;
                    }
                }

                // 2. 获取泛型类型 T 的所有属性
                PropertyInfo[] properties = typeof(T).GetProperties();

                // 3. 遍历数据行（从第二行开始）
                for (int row = 2; row <= rowCount; row++)
                {
                    try
                    {
                        T item = new T();

                        // 4. 动态映射列数据到属性
                        foreach (PropertyInfo prop in properties)
                        {
                            if (headers.ContainsKey(prop.Name))
                            {
                                int colIndex = headers[prop.Name];
                                string cellValue = worksheet.Cells[row, colIndex].Text;

                                if (!string.IsNullOrEmpty(cellValue))
                                {
                                    // 5. 类型转换（支持基本类型和 Nullable）
                                    object value = null;
                                    if (prop.PropertyType == typeof(string))
                                    {
                                        value = cellValue;
                                    }
                                    else
                                    {
                                        Type targetType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                                        value = Convert.ChangeType(cellValue, targetType);
                                    }

                                    prop.SetValue(item, value);
                                }
                            }
                        }

                        itemsList.Add(item);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"第 {row} 行数据转换失败: {ex.Message}");
                    }
                }
            }

            return itemsList;
        }

        public static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable dt = new DataTable();
            Type type = typeof(T);

            // 获取所有公共属性
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            // 创建列（仅处理可读属性）
            foreach (PropertyInfo prop in properties)
            {
                if (prop.CanRead)
                {
                    Type columnType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                    dt.Columns.Add(prop.Name, columnType);
                }
            }

            // 填充数据
            foreach (T item in list)
            {
                DataRow row = dt.NewRow();
                foreach (PropertyInfo prop in properties)
                {
                    if (prop.CanRead)
                    {
                        object value = prop.GetValue(item, null);
                        row[prop.Name] = value ?? DBNull.Value;
                    }
                }
                dt.Rows.Add(row);
            }

            return dt;
        }
    }
}
